package com.epam.dao.h2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.epam.dao.CourseDao;
import com.epam.dao.h2.exceptions.MethodNotDefinedExseption;
import com.epam.database.ConnectionPool;
import com.epam.entity.Course;

public class H2CourseDao extends H2AbstractEntityDao<Course> implements CourseDao<Course> {
	
	private static Logger LOGGER = LoggerFactory.getLogger(H2CourseDao.class);
	
	private final static String SELECT_BY_ID = "Select c.courSE_ID, c.course_NAME, "
			+ "p.perSON_NAME , c.trAINING_PERIOD, c.hours from COURSES as c left join teachers as t "
			+ " left join persons as p on t.person_id = p.person_id on c.teacher_id = t.teacher_id "
			+ " where course_id=?  order by course_id";
	private final static String SELECT_ALL = "Select c.courSE_ID, c.course_NAME, "
			+ " p.perSON_NAME , c.trAINING_PERIOD, c.hours from COURSES as c left join teachers as t "
			+ " left join persons as p on t.person_id = p.person_id on c.teacher_id = t.teacher_id "
			+ " order by c.course_id";

	@Override
	public Course selectById(long id) {
		Course course = null;
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			conn = ConnectionPool.getInstance().takeConnection();
			pst = conn.prepareStatement(SELECT_BY_ID);
			pst.setLong(1, id);
			rs = pst.executeQuery();
			if (rs.next()) {
				course = new Course(id, rs.getString("course_name"),
						rs.getString("person_name"),
						rs.getString("training_period"), rs.getInt("hours"));
			}
		} catch (Exception e) {
			LOGGER.warn("Failed to create the object");
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (conn != null) {
					
					ConnectionPool.getInstance().returnConnection(conn);
				}
			} catch (SQLException e) {
				LOGGER.error("Error  closing request SelectCourseById");
			}
		}
		return course;
	}

	@Override
	public List<Course> selectAll() {
		List<Course> courseList = null;
		Course course = null;
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			conn = ConnectionPool.getInstance().takeConnection();
			pst = conn.prepareStatement(SELECT_ALL);
			rs = pst.executeQuery();
			courseList = new ArrayList<Course>();
			while (rs.next()) {
				course = new Course(Long.valueOf(rs.getString("course_id")),
						rs.getString("course_name"),
						rs.getString("person_name"),
						rs.getString("training_period"), rs.getInt("hours"));
				courseList.add(course);
			}

		} catch (Exception e) {
			LOGGER.warn("Failed to create the list");
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (conn != null) {
					
					ConnectionPool.getInstance().returnConnection(conn);
				}
			} catch (SQLException e) {
				LOGGER.error("Error  closing request SelectAllCourses");
			}
		}

		return courseList;
	}

	@Override
	public void saveOrUpdate(Course entity) {
		try {
			throw new MethodNotDefinedExseption();
		} catch (MethodNotDefinedExseption e) {
			LOGGER.error("Method saveOrUpdate for Course not define");
		}

	}

	@Override
	public boolean delete(long id) {
		try {
			throw new MethodNotDefinedExseption();
		} catch (MethodNotDefinedExseption e) {
			LOGGER.error("Method delete for Course not define");
		}
		return false;
	}

	@Override
	public int insert(long id) {
		try {
			throw new MethodNotDefinedExseption();
		} catch (MethodNotDefinedExseption e) {
			LOGGER.error("Method saveOrUpdate for Course not define");
		}
		return 0;
	}

}
